/*****************************************************************************
  Copyright ?2002-2004 by Martin Cook. All rights are reserved. If you like
  this code then feel free to go ahead and use it. The only thing I ask is 
  that you don't remove or alter my copyright notice. Your use of this 
  software is entirely at your own risk. I make no claims about the 
  reliability or fitness of this code for any particular purpose. If you 
  make changes or additions to this code then please clearly mark your code 
  as yours. If you have questions or comments then please contact me at: 
  martin@codegator.com
  
  Have Fun! :o)
*****************************************************************************/


using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;

using Microsoft.ApplicationBlocks.Data.OleDb;

using CG.Security.Configuration;
using CG.Security.Data.Configuration;

namespace CG.Security.Data.Access
{
	
	/// <summary>
	/// An Access specific implementation of ISecurityData.
	/// </summary>
	internal sealed class SecurityData : ISecurityData
	{

		// *******************************************************************
		// Attributes.
		// *******************************************************************

		#region Attributes.

		/// <summary>
		/// The SQL to authenticate a user.
		/// </summary>
		private static readonly string SQL_AUTHENTICATE = 
			"SELECT user_id FROM cg_security_user " +
			"WHERE user_name = @user_name AND user_pwd = @user_pwd";

		/// <summary>
		/// The SQL to update the last login date for a user.
		/// </summary>
		private static readonly string SQL_UPDATE_LAST_LOGIN = 
			"UPDATE cg_security_user SET last_login = @last_login " +
			"WHERE user_id = @user_id";

		/// <summary>
		/// Various parameter names.
		/// </summary>
		private static readonly string PARM_USER_ID = "@user_id";
		private static readonly string PARM_USER_NAME = "@user_name";
		private static readonly string PARM_USER_PWD = "@user_pwd";
		private static readonly string PARM_LAST_LOGIN = "@last_login";

		#endregion
		
		// ******************************************************************
		// ISecurityData implementation.
		// ******************************************************************

		#region ISecurityData implementation.

		/// <summary>
		/// Authenticates the supplied credentials by checking them against
		/// the cg_security_user table in the database.
		/// </summary>
		/// <param name="userName">The user name.</param>
		/// <param name="userPwd">The user password (must be hashed).</param>
		/// <returns>True if the credentials authenticate, false otherwise.</returns>
		public bool Authenticate(
			string userName,
			string userPwd
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_AUTHENTICATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_NAME, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_USER_PWD, OleDbType.VarChar, 50)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_AUTHENTICATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userName;
			parms[1].Value = userPwd;

			// Execute the SQL statement.
			object userID = OleDbHelper.ExecuteScalar(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_AUTHENTICATE, 
				parms
				);

			// Did we fail to validate the user?
			if (userID == null || (Int32)userID == 0)
				return false;

			// Update the properties.
			UpdateLastLogin((Int32)userID);
            
			return true;

		} // End Authenticate()

		// ******************************************************************

		/// <summary>
		/// Returns all the effective rights for the specified user.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <returns>A DataSet containing all the effective rights.</returns>
		public System.Data.DataSet EffectiveRights(
			int userID
			)
		{

			// NOTE: normally, we would produce this information with a 
			//   query or a call to a stored procedure. However, Access 
			//   doesn't support stored procedures, and it pukes when
			//   I try to feed it the SQL to produce the desired result
			//   set (too many left joins), so we are stuck with this
			//   approach.
            
			// Get the list of roles for the user.
			DataSet userRoles = UserRoleManager.FindByUser(
				userID
				);

			ArrayList temp = new ArrayList();
			
			// Loop through all the roles assigned to the user.
			foreach (DataRow userRole in userRoles.Tables[0].Rows)
			{

				// Get the identifier for the row.
				int roleID = (Int32)userRole["role_id"];

				// Find all the rights assigned to the role.
				DataSet roleRights = RoleRightManager.FindByRole(
					roleID
					);
                
				// Loop through all the rights assigned to the role.
				foreach (DataRow roleRight in roleRights.Tables[0].Rows)
				{

					// Get the name of the right.
					string rightName = (string)roleRight["right_name"];

					// Should we store it?
					if (temp.IndexOf(rightName) == -1)
						temp.Add(rightName);
                    
				} // End foreach right assigned to this role.

			} // End foreach role assigned to the user.

			// Get the list of individual rights for the user.
			DataSet userRights = UserRightManager.FindByUser(
				userID
				);
            
			// Loop through all the rights associated with the user.
			foreach (DataRow userRight in userRights.Tables[0].Rows)
			{

				// Get the enable flag for the right.
				bool enableFlag = (bool)userRight["enable_flag"];

				// Get the name of the right.
				string rightName = (string)userRight["right_name"];

				// Should we add the right - or remove it.
				if (enableFlag)
				{
				
					// Should we add the right?
					if (temp.IndexOf(rightName) == -1)
						temp.Add(rightName);

				} // End if we should add the right.
				else
					temp.Remove(rightName);
                
			} // End foreach right associated with the user.

			// Create a dataset to return the list in.
			DataSet rights = new DataSet();
            rights.Tables.Add();
			rights.Tables[0].Columns.Add("right_name", typeof(string));

			// Loop and populate the dataset.
			foreach(string right in temp)
				rights.Tables[0].LoadDataRow(new object[] {right}, true);

			// Return the results.
			return rights;

		} // End EffectiveRights()

		#endregion 

		// ******************************************************************
		// Private methods.
		// ******************************************************************

		#region Private methods

		/// <summary>
		/// Used to update the last_login column in the cg_security_user table
		/// for the specified user.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		private void UpdateLastLogin(
			int userID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_UPDATE_LAST_LOGIN
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_LAST_LOGIN, OleDbType.DBTimeStamp),
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_UPDATE_LAST_LOGIN, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = "#" + DateTime.Now + "#";
			parms[1].Value = userID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_UPDATE_LAST_LOGIN, 
				parms
				);

		} // End UpdateLastLogin()

		#endregion

	} // End class SecurityData

} // End namespace CG.Security.Data.Access

